package query.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import query.bean.Answer;
import query.bean.Paper;
import query.db.PaperRepository;

/**
 * 答卷数据库操作接口实现类
 * 
 * @author lzy
 * @version 1.0.4
 *
 */
@Repository
public class JdbcPaperRepository implements PaperRepository {

	private JdbcTemplate jdbc;
	private final static String SELECT_ID = "SELECT id FROM PaperId ORDER BY id LIMIT 0,1";
	private final static String DELETE_ID = "DELETE FROM PaperId WHERE id = ? ";
	private final static String INSERT_ID = "INSERT INTO PaperId (id)VALUES(?)";
	private final static String INSERT_PAPER = "INSERT INTO Paper (id,questionnaireId,submitTime)VALUES(?,?,?)";
	private final static String DELETE_PAPER = "DELETE FROM Paper WHERE id = ?";
	private final static String INSERT_ANSWER = "INSERT INTO Answer (paperId,type,orderNumber,answerA,answerB,answerC,answerD)VALUES(?,?,?,?,?,?,?)";
	private final static String DELETE_ANSWER = "DELETE FROM Answer WHERE paperId = ?";
	private final static String SEARCH_ANSWER_BY_PAPERID = "SELECT* FROM Answer WHERE paperId = ? ORDER BY orderNumber";
	private final static String SEARCH = "SELECT* FROM Paper WHERE questionnaireId = ?";
	private final static String SEARCH_PAPER = "SELECT* FROM Paper WHERE questionnaireId = ? ORDER BY id DESC LIMIT ";
	private final static String SEARCH_PAPER_BY_ID = "SELECT* FROM Paper WHERE id = ?";

	/**
	 * 构造方法注入
	 * 
	 * @param jdbc
	 */
	@Autowired
	public JdbcPaperRepository(JdbcTemplate jdbc) {
		this.jdbc = jdbc;
	}

	/**
	 * 创建一个答卷
	 * 
	 * @param questionnaireId 答卷对应的问卷的id
	 * @return 创建好的答卷对象
	 */
	@Override
	public Paper insertPaper(long questionnaireId) {
		long id = this.getPaperId();
		if (id == -1) {
			return null;
		}
		Date date = new Date();
		Paper paper = new Paper(id, questionnaireId, date);
		jdbc.update(INSERT_PAPER, id, questionnaireId, date);
		return paper;
	}

	/**
	 * 新建一条回答
	 * 
	 * @param answer 要创建的回答对象
	 * @return 操作结果
	 */
	@Override
	public boolean insertAnswer(Answer answer) {
		jdbc.update(INSERT_ANSWER, answer.getPaperId(), answer.getType(), answer.getOrderNumber(), answer.getAnswerA(),
				answer.getAnswerB(), answer.getAnswerC(), answer.getAnswerD());
		return true;
	}

	/**
	 * 删除一个答卷的所有回答
	 * 
	 * @param paperId 答卷id
	 * @return 操作结果
	 */
	@Override
	public boolean deleteAnswer(long paperId) {
		jdbc.update(DELETE_ANSWER, paperId);
		return true;
	}

	/**
	 * 删除一个答卷
	 * 
	 * @param paperId 答卷id
	 * @return 操作结果
	 */
	@Override
	public boolean deletePaper(long paperId) {
		this.deleteAnswer(paperId);
		jdbc.update(DELETE_PAPER, paperId);
		jdbc.update(INSERT_ID, paperId);
		return true;
	}

	/**
	 * 删除一个问卷的所有答卷
	 * 
	 * @param questionnaireId 问卷id
	 * @return 操作结果
	 */
	@Override
	public boolean deletePapers(long questionnaireId) {
		List<Paper> list = this.searchPaper(questionnaireId);
		if (list == null) {
			return false;
		}
		for (int i = 0; i < list.size(); i++) {
			this.deletePaper(list.get(i).getId());
		}
		return true;
	}

	/**
	 * 获取一个问卷的所有答卷
	 * 
	 * @param questionnaireId 问卷id
	 * @return 答卷列表
	 */
	@Override
	public List<Paper> searchPaper(long questionnaireId) {
		List<Paper> list;
		try {
			list = jdbc.query(SEARCH, new PaperRowMapper(), questionnaireId);
		} catch (Exception e) {
			return null;
		}
		return list;
	}

	/**
	 * 获取一个问卷指定下标的答卷(按答卷id排序)
	 * 
	 * @param questionnaireId 问卷id
	 * @param start           起始下标(该下标的问卷不包括在内)
	 * @param num             要获取的问卷数目
	 * @return 问卷列表
	 */
	@Override
	public List<Paper> searchPaper(long questionnaireId, int start, int num) {
		List<Paper> list = jdbc.query(SEARCH_PAPER + start + "," + num, new PaperRowMapper(), questionnaireId);
		return list;
	}

	/**
	 * 获取一个答卷的所有回答
	 * 
	 * @param paperId 答卷id
	 * @return 回答列表
	 */
	@Override
	public List<Answer> searchAnswer(long paperId) {
		List<Answer> list;
		try {
			list = jdbc.query(SEARCH_ANSWER_BY_PAPERID, new AnswerRowMapper(), paperId);
		} catch (Exception e) {
			return null;
		}
		return list;
	}

	/**
	 * 根据id搜寻答卷
	 * 
	 * @param paperId 答卷id
	 * @return 搜索结果
	 */
	@Override
	public Paper search(long paperId) {
		Paper paper = jdbc.queryForObject(SEARCH_PAPER_BY_ID, new PaperRowMapper(), paperId);
		return paper;
	}

	/**
	 * 生成一个答卷id
	 * 
	 * @return 答卷id
	 */
	@SuppressWarnings("deprecation")
	private long getPaperId() {
		long id;
		try {
			id = jdbc.queryForLong(SELECT_ID);
			jdbc.update(DELETE_ID, id);
		} catch (Exception e) {
			return -1;
		}
		return id;
	}

	/**
	 * 用于查询答卷
	 * 
	 * @author lzy
	 * @version 1.0.4
	 *
	 */
	private static final class PaperRowMapper implements RowMapper<Paper> {

		@Override
		public Paper mapRow(ResultSet rs, int rowNum) throws SQLException {
			return new Paper(rs.getLong("id"), rs.getLong("questionnaireId"), rs.getDate("submitTime"));
		}

	}

	/**
	 * 用于查询回答
	 * 
	 * @author lzy
	 * @version 1.0.4
	 *
	 */
	private static final class AnswerRowMapper implements RowMapper<Answer> {

		@Override
		public Answer mapRow(ResultSet rs, int rowNum) throws SQLException {
			return new Answer(rs.getLong("paperId"), rs.getInt("type"), rs.getInt("orderNumber"),
					rs.getString("answerA"), rs.getString("answerB"), rs.getString("answerC"), rs.getString("answerD"));
		}

	}
}
